package com.coderman.utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * Created on 2017-4-7.
 * @author
 * @version: V1.0
 * @Desc: JDBC简单工具类
 */
public class JdbcUtils {
    private  static  final Logger log = LoggerFactory.getLogger(JdbcUtils.class);

    private static Properties properties = new Properties();
    private static Connection connection = null;
    private static String dbName = "";


    private static void init(String configPath){
        //已经初始化过之后不用再初始化
        if(!properties.isEmpty() ){
            return;
        }
        InputStream is;
        try {
            is = new FileInputStream(new File(configPath));
            properties.load(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化db连接
     * @return
     * @throws Exception
     */
    public static Connection initDBConnection(String configPath)throws Exception {

        init(configPath);

        // 1. 准备获取连接的 4 个字符串: user, password, url, jdbcDriver
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url= properties.getProperty("url");
        String jdbcDriver= properties.getProperty("jdbcDriver");

        // 2. 加载驱动: Class.forName(driverClass)
        Class.forName(jdbcDriver);
        // 3.获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        return  connection;
    }


    /**
     * 执行单条语句
     * @param sql
     * @return
     * @throws Exception
     */
    public static boolean exeOneSql(String sql)throws Exception {
        if(connection == null){
            log.error("connection is null ,please init it....");
            return false;
        }
        //保存当前自动提交模式
        boolean autoCommit = connection.getAutoCommit();
        //关闭自动提交
        connection.setAutoCommit(false);
        try {
            connection.prepareStatement(sql).execute();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;

        }catch (Exception e){
            connection.rollback();
            return false;
        }

    }

    /**
     * 批量执行多条语句
     * @param sqlList
     * @return
     * @throws Exception
     */
    public static boolean exeBatchSql(List<String> sqlList) throws Exception {
        try {
            if(connection == null){
                log.error("connection is null ,please init it....");
                return false;
            }
            //保存当前自动提交模式
            boolean autoCommit=connection.getAutoCommit();
            //关闭自动提交
            connection.setAutoCommit(false);
            Statement preparedStatement = connection.createStatement();

            for (String sql : sqlList){
                preparedStatement.addBatch(sql);
            }
            preparedStatement.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);
            return true;
        } catch (Exception e) {
            log.error("query error",e);
            connection.rollback();
            return false;
        }
    }


    public static  int getCountBySql(String sql){
        Statement preparedStatement = null;
        try {
            if(connection == null){
                log.error("connection is null ,please init it....");
                return -1;
            }
            preparedStatement = connection.createStatement();
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            if(resultSet.next()) {
                int count = resultSet.getInt(1);
                return count;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }


    /**
     * 根据sql查询key--value形式的map
     * @param querySql
     * sql eg: select keyColumn,valueColumn from table
     * @return
     */
    public static Map<String, String> exeQuerySql(String querySql){
        Map<String, String> map = new HashMap<String, String>();
        Statement preparedStatement = null;
        try {
            if(connection == null){
                log.error("connection is null ,please init it....");
                return null;
            }
            preparedStatement = connection.createStatement();
            ResultSet resultSet = preparedStatement.executeQuery(querySql);
            while (resultSet.next()) {
                map.put(resultSet.getObject(1)+"",resultSet.getObject(2)+"");
            }

            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static String getDbName(){

        return properties.getProperty("dbName");

    }

}
